<?php
namespace org\crawlgenie\models\dao;
use org\crawlgenie\application\base\Factory;
use org\crawlgenie\models\JobListing;
use \Logger;
use \CGException;

/**
 *  Data Access Object for JobListing Model
 *
 *  @author $Author: jinsonxu@gmail.com $:
 *  $Date: 2014-11-16 10:39:12 +0000 (Sun, 16 Nov 2014) $:
 *  $Id: JobListingDAO.php 16 2014-11-16 10:39:12Z jinsonxu@gmail.com $:
 *  @version $Rev: 16 $:
 *  @package crawlgenie.models
 *  @subpackage dao
 */


class JobListingDAO {

    
    
    public static function load($id) {
        $db = Factory::getDBConn();

        if(self::exists($id, null)) {

            $query = "select * from "._CG_REPOS_JOBS_INTERMEDIATE_TABLE." where "
                    ."id = ?";
            $queryValues = array($id);

            Logger::getLogger(__CLASS__)->info(__METHOD__ . ': Job Listing Load Query: ' . print_r($query, true));
            Logger::getLogger(__CLASS__)->info(__METHOD__ . ': Job Listing Load Values: ' . print_r($queryValues, true));

            $db->preparedQuery($query, $queryValues);
            return($db->getRow());
            
        } else {
            throw new \CGException('Specified JobListing database ID "'.$id.'" does not exist');
        }
    }
    

 
    
    /**
     * This function stores the parsed job listing into the 
     * repository jobs intermediate table in db 
     * 
     * @param JobListing $obj
     */
    public static function store(JobListing $obj) {
        $db = Factory::getDBConn();

        if(self::exists(null, $obj->getMetadataID())) {
           throw new \CGException('Job listing for meta object ID "'. $obj->getMetadataID() . '" already exists. Unable to store as new row.');
        }
        
        $jobListingInsertQuery = "insert into "._CG_REPOS_JOBS_INTERMEDIATE_TABLE."
                                (metadata_id, listing_id, post_date, close_date, 
                                listing_company_name, listing_title, description, 
                                requirements, industry, category, employment_type, 
                                working_hours, shift_pattern, min_salary, max_salary,
                                job_level, min_years_experience, location, address, 
                                postal_code, contact_telephone, contact_email, 
                                vacancies, viewed, applied, last_updated) 
                                values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?, NOW())";
        $jobListingInsertValues = array($obj->getMetadataID(), $obj->getListingID(),
            $obj->getPostDate(), $obj->getCloseDate(), $obj->getListingCompanyName(), 
            $obj->getListingTitle(), $obj->getDescription(), $obj->getRequirements(), 
            $obj->getIndustry(), $obj->getCategory(), $obj->getEmploymentType(), 
            $obj->getWorkingHours(), $obj->getShiftPattern(), $obj->getMinSalary(), $obj->getMaxSalary(), 
            $obj->getJobLevel(), $obj->getMinYearsExperience(), $obj->getLocation(), $obj->getAddress(), 
            $obj->getPostalCode(), $obj->getContactTelephone(), $obj->getContactEmail(), 
            $obj->getVacancies(), $obj->getViewed(), $obj->getApplied());

        Logger::getLogger(__CLASS__)->info(__METHOD__ . ': Job Listing Insertion Query: ' . print_r($jobListingInsertQuery, true));
        Logger::getLogger(__CLASS__)->info(__METHOD__ . ': Job Listing Insertion Values: ' . print_r($jobListingInsertValues, true));

        $db->preparedQuery($jobListingInsertQuery, $jobListingInsertValues);        
    }
  
    
    
    
    

    
    
    public static function exists($id = null, $metadataID = null) {
        $db = Factory::getDBConn();
        $return = false;
        
        if(null == $id && null == $metadataID) {
            throw new \CGException('No id or metadata_id specified to check for existence');
            
        } elseif(null == $id) {
            $existsQuery = "select count(*) from "._CG_REPOS_JOBS_INTERMEDIATE_TABLE."
                where metadata_id = ?";
            $db->preparedQuery($existsQuery, array($metadataID));
            $return = ($db->getVar() > 0) ? true : false;
            
        } elseif(null == $metadataID) {
            $existsQuery = "select count(*) from "._CG_REPOS_JOBS_INTERMEDIATE_TABLE."
                where id = ?";
            $db->preparedQuery($existsQuery, array($id));
            $return = ($db->getVar() > 0) ? true : false;
            
        } else {
            $existsQuery = "select count(*) from "._CG_REPOS_JOBS_INTERMEDIATE_TABLE."
                where id=? and metadata_id = ?";
            $db->preparedQuery($existsQuery, array($id, $metadataID));
            $return = ($db->getVar() > 0) ? true : false;            
        }
        return $return;
    }

    
    
    public static function getNumUnProcessedRecords($batchID, $author) {
        $db = Factory::getDBConn();
        $query = "select count(*) from " . _CG_REPOS_METADATA_TABLE . " where batch_id=? and author=? "
                ."and id not in (select metadata_id as id from "._CG_REPOS_JOBS_INTERMEDIATE_TABLE. ")";
        $db->preparedQuery($query, array($batchID, $author));
        
        return $db->getVar();
    }
    
    
    public static function getAllUnProcessedRows($batchID, $author, $limit = null, $offset = null) {
        $db = Factory::getDBConn();
        $limitStatement = (null !== $limit && null !== $offset) ? " limit ".$limit." offset " . $offset : '';

        $query = "select * from " . _CG_REPOS_METADATA_TABLE . " where batch_id=? and author=? "
                ."and id not in (select metadata_id as id from "._CG_REPOS_JOBS_INTERMEDIATE_TABLE. ") order by id" 
                . $limitStatement;
        $queryValues = array($batchID, $author);

        Logger::getLogger(__CLASS__)->info(__METHOD__ . ': Unprocessed rows Query: ' . print_r($query, true));
        Logger::getLogger(__CLASS__)->info(__METHOD__ . ': Unprocessed rows Values: ' . print_r($queryValues, true));

        $db->preparedQuery($query, $queryValues);
        return $db->getResults();        
    }
    
    

}
?>
